/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Conexiones;

import control.Licencia;
import control.Producto;
import control.Proveedor;
import control.Software;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author WILFREDO
 */
public class ConsultasBD {
    ConexionMySQL conexion;
    public ConsultasBD()
    {
        conexion = new ConexionMySQL("laboratorio","root","");
    }
    public ArrayList<String>  Productos ()  
    {   
        ArrayList <String> listaP= new ArrayList<String>();
        try {
            ResultSet rs=null;
            PreparedStatement psInsertar = null;
            String consultaInsert="SELECT distinct nombreHard FROM hardware ";
            psInsertar = conexion.getConnection().prepareStatement(consultaInsert);
            rs=psInsertar.executeQuery();
             while (rs.next()) 
            {
                listaP.add(rs.getString(1));
            }
        } catch (SQLException ex) {
           
         
        }
        
       return listaP;        
    }
    
     public ArrayList<String>  ProductosSof ()  
    {   
        ArrayList <String> listaP= new ArrayList<String>();
        try {
            ResultSet rs=null;
            PreparedStatement psInsertar = null;
            String consultaInsert="SELECT distinct nombreSof FROM software;";
            psInsertar = conexion.getConnection().prepareStatement(consultaInsert);
            rs=psInsertar.executeQuery();
             while (rs.next()) 
            {
                listaP.add(rs.getString(1));
            }
        } catch (SQLException ex) {
           
         
        }
        
       return listaP;        
    }
    
    
     public boolean insertarhasProveedorProductos (int codigoProv,int codigoProd) 
    {  
        
        String con="";
        String consultaInsert;
        try {
            
            PreparedStatement insertarP = null;
            consultaInsert="insert into proveedores_has_hardware values (?,?);";
            insertarP = conexion.getConnection().prepareStatement(consultaInsert);
            insertarP.setInt(1,codigoProv);
            insertarP.setInt(2,codigoProd);
            
            insertarP.executeUpdate();
            return true;     
            
        } catch (Exception ex) {   return false;  }
       
    }
    
      public boolean insertarLicencia (Licencia pro) 
    {  
        
        String con="";
        String consultaInsert;
        try {
            
            PreparedStatement insertarP = null;
            consultaInsert="insert into licencia values (?,?,?,?,?,?);";
            insertarP = conexion.getConnection().prepareStatement(consultaInsert);
            insertarP.setInt(1,pro.getCodigo());
            insertarP.setInt(2,pro.getCodigoSoftware());
            insertarP.setInt(3,pro.getEstado());
            insertarP.setString(4,pro.getNumeroLic());
            insertarP.setString(5,pro.getDateFechaInicio());
            insertarP.setString(6,pro.getDateFechaFin());
            insertarP.executeUpdate();
            return true;     
            
        } catch (Exception ex) {   return false;  }
       
    }
     
     
     
     public boolean insertarProductos (Producto pro) 
    {  
        
        String con="";
        String consultaInsert;
        try {
            
            PreparedStatement insertarP = null;
            consultaInsert="insert into hardware values (?,NULL,?,4,NULL,?);";
            insertarP = conexion.getConnection().prepareStatement(consultaInsert);
            insertarP.setInt(1,pro.getCodigo());
            insertarP.setString(2,pro.getNombre());
            insertarP.setString(3,pro.getNumeroSerie());
            insertarP.executeUpdate();
            return true;     
            
        } catch (Exception ex) {   return false;  }
       
    }
     public boolean insertarProductosS (Software soft) 
    {  
        
        String con="";
        String consultaInsert;
        try {
            
            PreparedStatement insertarP = null;
            consultaInsert="insert into software values (?,?,?);";
            insertarP = conexion.getConnection().prepareStatement(consultaInsert);
            insertarP.setInt(1,soft.getCodigo());
            insertarP.setInt(2,soft.getProve());
            insertarP.setString(3,soft.getNombre());
            insertarP.executeUpdate();
            return true;     
            
        } catch (Exception ex) {   return false;  }
       
    }
     
    
    
    public ArrayList<String>  Proveedores ()  
    {   
        ArrayList <String> listaP= new ArrayList<String>();
        try {
            ResultSet rs=null;
            PreparedStatement psInsertar = null;
            String consultaInsert="SELECT nombrePro FROM proveedores where tipo='H'";
            psInsertar = conexion.getConnection().prepareStatement(consultaInsert);
            rs=psInsertar.executeQuery();
             while (rs.next()) 
            {
                listaP.add(rs.getString(1));
            }
        } catch (SQLException ex) {
           
         
        }
        
       return listaP;        
    }
    
       public ArrayList<String>  ProveedoresS ()  
    {   
        ArrayList <String> listaP= new ArrayList<String>();
        try {
            ResultSet rs=null;
            PreparedStatement psInsertar = null;
            String consultaInsert="SELECT nombrePro FROM proveedores where tipo='S'";
            psInsertar = conexion.getConnection().prepareStatement(consultaInsert);
            rs=psInsertar.executeQuery();
             while (rs.next()) 
            {
                listaP.add(rs.getString(1));
            }
        } catch (SQLException ex) {
           
         
        }
        
       return listaP;        
    }
    
    
    public ArrayList<Producto>  tablaProductos ()  
    {   
        ArrayList <Producto> listaP= new ArrayList<Producto>();
        try {
            ResultSet rs=null;
            PreparedStatement psInsertar = null;
            String consultaInsert="SELECT nombreHard,hardserie FROM hardware ";
            psInsertar = conexion.getConnection().prepareStatement(consultaInsert);
            rs=psInsertar.executeQuery();
            while (rs.next()) 
            {
                listaP.add(new Producto(0,rs.getString(1),rs.getString(2)));
            }
        } catch (SQLException ex) {
           
         
        }
        
       return listaP;        
    }
    
    
    public ArrayList<Software>  tablaProductosS ()  
    {   
        ArrayList <Software> listaP= new ArrayList<Software>();
        try {
            ResultSet rs=null;
            PreparedStatement psInsertar = null;
            String consultaInsert="SELECT nombrePro, nombreSof FROM software,proveedores where software.PROVEEDORES_idPro =proveedores.idPro";
            psInsertar = conexion.getConnection().prepareStatement(consultaInsert);
            rs=psInsertar.executeQuery();
            while (rs.next()) 
            {
                Software nuevo = new Software(0,rs.getString(1));
                nuevo.setProveedor(rs.getString(2));
                listaP.add(nuevo);
            }
        } catch (SQLException ex) {
           
         
        }
        
       return listaP;        
    }
    
    public ArrayList<String>  tipoTransaccion ()  
    {   
        ArrayList <String> listaP= new ArrayList<String>();
        try {
            ResultSet rs=null;
            PreparedStatement psInsertar = null;
            String consultaInsert="SELECT  nombreTipTra FROM `tipo transaccion`";
            psInsertar = conexion.getConnection().prepareStatement(consultaInsert);
            rs=psInsertar.executeQuery();
            while (rs.next()) 
            {
                listaP.add(rs.getString(1));
            }
        } catch (SQLException ex) {
           
         
        }
        
       return listaP;        
    }
    
    
     public int codigoLicencia () 
    {  
        
        ResultSet rs=null;
        int codigo=-1;
        String consultarCodigo;
        try {
            
            PreparedStatement consultarP = null;
            consultarCodigo="select max(idLic) from licencia";
            consultarP = conexion.getConnection().prepareStatement(consultarCodigo);
            rs=consultarP.executeQuery();
            while (rs.next())
            codigo = rs.getInt(1);
            
            
        } catch (Exception ex) {    }
        if (codigo==-1)
        {   
            return -1;
        }
        else 
            return codigo+1;
       
    }
   
    
     public int codigoHardware () 
    {  
        
        ResultSet rs=null;
        int codigo=-1;
        String consultarCodigo;
        try {
            
            PreparedStatement consultarP = null;
            consultarCodigo="select max(idHar) from hardware";
            consultarP = conexion.getConnection().prepareStatement(consultarCodigo);
            rs=consultarP.executeQuery();
            while (rs.next())
            codigo = rs.getInt(1);
            
            
        } catch (Exception ex) {    }
        if (codigo==-1)
        {   
            return -1;
        }
        else 
            return codigo+1;
       
    }
     
     public int codigoSoftware () 
    {  
        
        ResultSet rs=null;
        int codigo=-1;
        String consultarCodigo;
        try {
            
            PreparedStatement consultarP = null;
            consultarCodigo="select max(idSof) from software";
            consultarP = conexion.getConnection().prepareStatement(consultarCodigo);
            rs=consultarP.executeQuery();
            while (rs.next())
            codigo = rs.getInt(1);
            
            
        } catch (Exception ex) {    }
        if (codigo==-1)
        {   
            return -1;
        }
        else 
            return codigo+1;
       
    }
    
      public int codigoProveedor (String nombre) 
    {  
        
        ResultSet rs=null;
        int codigo=-1;
        String consultarCodigo;
        try {
            
            PreparedStatement consultarP = null;
            consultarCodigo="SELECT idPro FROM proveedores where nombrePro=?";
            consultarP = conexion.getConnection().prepareStatement(consultarCodigo);
            consultarP.setString(1,nombre);
            rs=consultarP.executeQuery();
            while (rs.next())
            codigo = rs.getInt(1);
            
            
        } catch (Exception ex) {    }
        
            return codigo;
       
    }
     
     public int codigoSoftware(String nombre) 
    {  
        
        ResultSet rs=null;
        int codigo=-1;
        String consultarCodigo;
        try {
            
            PreparedStatement consultarP = null;
            consultarCodigo="SELECT idSoft FROM software where nombreSof=?";
            consultarP = conexion.getConnection().prepareStatement(consultarCodigo);
            consultarP.setString(1,nombre);
            rs=consultarP.executeQuery();
            while (rs.next())
            codigo = rs.getInt(1);
            
            
        } catch (Exception ex) {    }
        
            return codigo;
       
    }
    
     public static void main (String []args)
     {
            ConsultasBD proveedor= new ConsultasBD();
            System.out.print(proveedor.Productos().get(0));
     }
    
    
    
}
